package com.apestech.api.condition;

import com.apestech.formwork.condition.ConditionUtil;
import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.DBPub;
import com.apestech.framework.util.StringUtil;
import java.util.ArrayList;

public class ConditionDataTool extends StringUtil {
    private SqlSession session;

    public ConditionDataTool(SqlSession session){
        this.session=session;
    }

    public void dataTableExec() throws Exception {
//        bakData();  //备份全部原始数据
//        bakDim();  //备份维度表
//        createDim();  //重新建维度数据
//        deleteEmptyTable();  //初始化调数日志表，删除空表
//        bakDataTable();  //备份有数据的表
//        createConditionTable();  //创建条件策略表
//        restoreData();  //恢复数据
        //针对有数据的主表和明细表初始化主键
//        deleteOtherTable();  //删除多余表（非新逻辑创建表）
//        restoreSeq();  //序列处理
//        updateExecData();
//        insertOtherField();
        //insertBillDataFromExec();
        createISTable();
    }

    private void insertBillDataFromExec() throws Exception {
        ConditionUtil.getAllDimensionDef(session);
        ConditionUtil.getAllCondition(session);
        DBPub.resetSeq(session, "EXE_CONDITION_SUM");
        session.getForUpdate().update("DELETE EXE_CONDITION_SUM");
        JSONArray clist=ConditionUtil.getCondition(session);
        int len=clist.size();
        for(int i=0; i<len; i++){
            JSONObject con=clist.get(i);
            String id=con.getString("ID");
            ConditionExec exec=new ConditionExec(session, id);
            exec.getConOper().getConSave().execSave();
        }
    }

    private void insertOtherField() throws Exception {
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%' AND TABLE_TYPE='TABLE'\n" +
                "  AND (TABLE_NAME NOT LIKE '%_ITEM%' AND TABLE_NAME NOT LIKE '%_EXEC%')";
        JSONObject p=new JSONObject();
        p.put("printSql","false");
        session.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tabname=rs.getString("TABLE_NAME");
                    if(countString(tabname,"_")==1){
                        if(tabname.indexOf("Z_")==0){
                            Table tab=session.getDictTable(tabname);
                            String col=tab.getColumn();
                            if(col.indexOf("IMP_ID")<0){
                                session.getForDDL().getAlterAddColumn(tabname, "IMP_ID", "VARCHAR2(32)", "导入编码");
                            }
                            if(col.indexOf("MEMO")<0){
                                session.getForDDL().getAlterAddColumn(tabname, "MEMO", "VARCHAR2(500)", "备注");
                                session.getForDDL().getAlterAddColumn(tabname+"_ITEM", "MEMO", "VARCHAR2(500)", "备注");
                                session.getForDDL().getAlterAddColumn(tabname+"_EXEC", "MEMO", "VARCHAR2(500)", "备注");
                            }
                        }
                    }
                }
            }
        });
    }

    private void updateExecData() throws Exception {
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%' AND TABLE_TYPE='TABLE'\n" +
                "  AND (TABLE_NAME NOT LIKE '%_ITEM%' AND TABLE_NAME NOT LIKE '%_EXEC%')";
        JSONObject p=new JSONObject();
        p.put("printSql","false");
        session.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tabname=rs.getString("TABLE_NAME");
                    if(countString(tabname,"_")==1){
                        if(tabname.indexOf("Z_")==0){
                            String etab=tabname+"_EXEC";
                            session.getForUpdate().update("UPDATE "+etab+" SET STATE='exec'");
                        }
                    }
                }
            }
        });
    }


    private void bakData() throws Exception {
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%' AND TABLE_TYPE='TABLE'\n" +
                "  AND (TABLE_NAME NOT LIKE '%_ITEM%' AND TABLE_NAME NOT LIKE '%_EXEC%')";
        JSONObject p=new JSONObject();
        p.put("printSql","false");
        session.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tabname=rs.getString("TABLE_NAME");
                    if(countString(tabname,"_")==1){
                        if(tabname.indexOf("Z_")==0){
                            String ztab=tabname;
                            String itab=tabname+"_ITEM";
                            String etab=tabname+"_EXEC";
                            session.getForUpdate().update("CREATE TABLE TLBK_"+ztab+" AS SELECT * FROM "+ztab);
                            if(session.tableExists(itab)){
                                session.getForUpdate().update("CREATE TABLE TLBK_"+itab+" AS SELECT * FROM "+itab);
                            }
                            if(session.tableExists(etab)){
                                session.getForUpdate().update("CREATE TABLE TLBK_"+etab+" AS SELECT * FROM "+etab);
                            }
                        }
                    }
                }
            }
        });
    }

    private void bakDim() throws Exception {
        if(session.tableExists("HP_EXE_DIMENSION")){
            session.getForDDL().dropTable("HP_EXE_DIMENSION");
        }
        session.getForUpdate().update("create table HP_EXE_DIMENSION as select * from EXE_DIMENSION");
        session.getForDDL().truncateTable("EXE_DIMENSION");
        DBPub.resetSeq(session, "EXE_DIMENSION");
    }

    private void createDim() throws Exception {
        ConditionUtil.getAllDimensionDef(session);
        ConditionUtil.getAllCondition(session);
    }

    private void deleteEmptyTable() throws Exception {
        createBakExecTable();
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%' AND TABLE_TYPE='TABLE'\n" +
                "  AND (TABLE_NAME NOT LIKE '%_ITEM%' AND TABLE_NAME NOT LIKE '%_EXEC%')";
        JSONObject p=new JSONObject();
        p.put("printSql","false");
        session.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tabname=rs.getString("TABLE_NAME");
                    if(countString(tabname,"_")==1){
                        if(tabname.indexOf("Z_")==0){
                            String ztab=tabname;
                            String itab=tabname+"_ITEM";
                            String etab=tabname+"_EXEC";
                            String mid=rightStr(ztab,3);
                            JSONArray list=session.getForJSON().query("SELECT FIELD FROM EXE_DIMENSION WHERE ID='"+mid+"'");
                            String FIELD=null;
                            if(list.get(0)!=null){
                                FIELD=list.get(0).getString("FIELD");
                            }
                            boolean zflag_e=session.tableExists(ztab);
                            boolean zflag=zflag_e;
                            if(zflag && session.getForResultSet().tableIsEmpty(ztab)) {
                                zflag=false;
                            }
                            boolean iflag_e=session.tableExists(itab);
                            boolean eflag_e=session.tableExists(etab);
                            boolean eflag=eflag_e;
                            if(eflag && session.getForResultSet().tableIsEmpty(etab)) {
                                eflag=false;
                            }
                            if(!zflag && !eflag){
                                System.out.println(tabname);
                                if(eflag_e) session.getForDDL().dropTable(etab);
                                if(iflag_e)session.getForDDL().dropTable(itab);
                                if(zflag_e) session.getForDDL().dropTable(ztab);
                                if(FIELD!=null){
                                    JSONObject map=new JSONObject();
                                    map.put("O_TAB_ID", ztab);
                                    map.put("O_ID",rightStr(ztab,3));
                                    map.put("OPER",0);
                                    session.getForUpdate().insertTable("HP_DIM_TZ", map);
                                }

                            }
                        }
                    }
                }
            }
        });
    }

    private void bakDataTable() throws Exception {
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%' AND TABLE_TYPE='TABLE'\n" +
                "  AND (TABLE_NAME NOT LIKE '%_ITEM%' AND TABLE_NAME NOT LIKE '%_EXEC%')";
        JSONObject p=new JSONObject();
        p.put("printSql","false");
        session.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tabname=rs.getString("TABLE_NAME");
                    if(countString(tabname,"_")==1){
                        if(tabname.indexOf("Z_")==0){
                            String ztab=tabname;
                            String itab=tabname+"_ITEM";
                            String etab=tabname+"_EXEC";
                            //String mid=rightStr(ztab,3);
                            boolean zflag_e=session.tableExists(ztab);
                            boolean iflag_e=session.tableExists(itab);
                            boolean eflag_e=session.tableExists(etab);
                            if(eflag_e) {
                                if(session.getForResultSet().tableIsEmpty(etab)){
                                    session.getForDDL().dropTable(etab);
                                } else {
                                    session.getForDDL().renameTable(etab, etab + "_BAK");
                                }
                            }
                            if(iflag_e) {
                                if(session.getForResultSet().tableIsEmpty(itab)){
                                    session.getForDDL().dropTable(itab);
                                } else {
                                    session.getForDDL().renameTable(itab, itab + "_BAK");
                                }
                            }
                            if(zflag_e) {
                                if(session.getForResultSet().tableIsEmpty(ztab)){
                                    session.getForDDL().dropTable(ztab);
                                } else {
                                    session.getForDDL().renameTable(ztab, ztab + "_BAK");
                                }
                            }
                        }
                    }
                }
            }
        });
    }

    private void createConditionTable() throws Exception {
        ConditionUtil.getAllDimensionDef(session);
        ConditionUtil.getAllCondition(session);
        ConditionUtil.createConditionTable(session, true);
    }

    private void restoreData() throws Exception {
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%_BAK' AND TABLE_TYPE='TABLE' ORDER BY TABLE_NAME ";
        session.getForResultSet().sql(sql, new QueryCallBackHandler() {
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tablename=rs.getString("TABLE_NAME");
                    int pos1=tablename.indexOf("_");
                    int pos2=tablename.indexOf("_", pos1+1);
                    String mid=tablename.substring(pos2-3,pos2);
                    String sql="SELECT B.ID AS O_ID,A.ID AS N_ID,A.FIELD\n" +
                            "FROM EXE_DIMENSION A, hp_EXE_DIMENSION B\n" +
                            "WHERE A.FIELD=B.FIELD AND B.ID='"+mid+"'";
                    JSONArray list=session.getForJSON().query(sql);
                    JSONObject m=list.get(0);
                    if(m!=null){  //能找到对照关系
                        String ntab=tablename.substring(0, pos2-3)+m.getString("N_ID")+tablename.substring(pos2, tablename.length()-4);
                        m.put("O_TAB_ID",tablename);
                        m.put("N_TAB_ID",ntab);
                        m.put("OPER",1);
                        session.getForUpdate().insertTable(tablename, ntab);
                        session.getForUpdate().insertTable("HP_DIM_TZ", m);
                        session.getForDDL().dropTable(tablename);
                    } else {
                        String ntab=tablename.substring(0, pos2-3)+mid+tablename.substring(pos2, tablename.length()-4);
                        m=new JSONObject();
                        m.put("O_ID", mid);
                        m.put("N_ID", mid);
                        m.put("O_TAB_ID",tablename);
                        m.put("N_TAB_ID",ntab);
                        m.put("OPER",2);
                        session.getForUpdate().insertTable(tablename, ntab);
                        session.getForUpdate().insertTable("HP_DIM_TZ", m);
                        session.getForDDL().dropTable(tablename);
                    }
                }
            }
        });
    }

    private void restoreSeq() throws Exception {
        String sql=" SELECT TABLE_ID FROM EXE_ID WHERE TABLE_ID LIKE 'Z_%' ";
        JSONObject p=new JSONObject();
        p.put("printSql","false");
        session.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tabname=rs.getString("TABLE_ID");
                    if(!session.tableExists(tabname)){
                        DBPub.dropSequence(session, tabname);
                    } else if(!session.getForResultSet().tableIsEmpty(tabname)){
                        DBPub.setSeqFromTable(session, tabname);
                        System.out.println("NO EMPTY: "+tabname);
                    } else {
                        DBPub.resetSeq(session, tabname);
                    }
                }
            }
        });
    }

    private void deleteOtherTable() throws Exception {
        int bh=22;  //执行之前先调它，非常重要
        session.getForUpdate().update("delete EXE_DIMENSION where id>'0"+bh+"'");
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%' AND TABLE_TYPE='TABLE'\n" +
                "  AND (TABLE_NAME NOT LIKE '%_ITEM%' AND TABLE_NAME NOT LIKE '%_EXEC%')";
        JSONObject p=new JSONObject();
        p.put("printSql","false");
        session.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String tabname=rs.getString("TABLE_NAME");
                    if(countString(tabname,"_")==1){
                        if(tabname.indexOf("Z_")==0 && !"_BAK".equals(rightStr(tabname,4))){
                            String tablename=rs.getString("TABLE_NAME");
                            String mid=rightStr(tablename,3);
                            if(castToInt(mid)>bh){
                                if(session.tableExists(tablename+"_EXEC")) session.getForDDL().dropTable(tablename+"_EXEC");
                                if(session.tableExists(tablename+"_ITEM")) session.getForDDL().dropTable(tablename+"_ITEM");
                                if(session.tableExists(tablename)) session.getForDDL().dropTable(tablename);
                            }
                        }
                    }
                }
            }
        });
    }

    //创建导数日志表
    private void createBakExecTable() throws Exception {
        if(session.tableExists("HP_DIM_TZ")) {
            session.getForDDL().truncateTable("HP_DIM_TZ");
        } else {
            ArrayList<String> list=new ArrayList<String>();
            list.add("create table HP_DIM_TZ (\n" +
                    "                o_tab_id VARCHAR2(32 CHAR) not null,\n" +
                    "                n_tab_id VARCHAR2(32 CHAR),\n" +
                    "                o_id     VARCHAR2(32 CHAR) not null,\n" +
                    "                n_id     VARCHAR2(32 CHAR),\n" +
                    "                oper     NUMBER(1),\n" +
                    "                field    VARCHAR2(4000 CHAR) default 1\n" +
                    ")");
            list.add("comment on table HP_DIM_TZ is '维度数据调整'");
            list.add("comment on column HP_DIM_TZ.o_tab_id is '老表ID'");
            list.add("comment on column HP_DIM_TZ.n_tab_id is '新表ID'");
            list.add("comment on column HP_DIM_TZ.o_id is '老维度ID'");
            list.add("comment on column HP_DIM_TZ.n_id is '新维度ID'");
            list.add("comment on column HP_DIM_TZ.oper is '操作(0:删除;1:对照调整;2:新建调整)'");
            list.add("comment on column HP_DIM_TZ.field is '维度字段'");
            list.add("alter table HP_DIM_TZ add constraint PK_HP_DIM_TZ primary key (O_TAB_ID)");
            session.getForDDL().executeDDL(list);
        }
    }

    //删除误备份的空表，选择用
    private void deleteEmptyBakTable() throws Exception {
        String sql="SELECT TABLE_NAME FROM USER_TAB_COMMENTS \n" +
                "WHERE TABLE_NAME LIKE 'Z_%_BAK' AND TABLE_TYPE='TABLE'";
        session.getForResultSet().sql(sql, new QueryCallBackHandler() {
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String ztab=rs.getString("TABLE_NAME");
                    if(session.getForResultSet().tableIsEmpty(ztab)){
                        session.getForDDL().dropTable(ztab);
                    }
                }
            }
        });
    }

    private void createISTable() throws Exception {
        String sql="SELECT A.TABLE_NAME FROM USER_TAB_COMMENTS A\n" +
                "WHERE A.TABLE_NAME LIKE 'Z_%_EXEC' AND A.TABLE_TYPE='TABLE'\n" +
                "      AND NOT EXISTS (SELECT 1 FROM USER_TAB_COMMENTS Z WHERE A.TABLE_NAME||'_I'=Z.TABLE_NAME)\n" +
                "      AND NOT EXISTS (SELECT 1 FROM USER_TAB_COMMENTS Z WHERE A.TABLE_NAME||'_STOP'=Z.TABLE_NAME) ";  //AND ROWNUM=1
        session.getForResultSet().sql(sql, new QueryCallBackHandler() {
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    String ztab=rs.getString("TABLE_NAME");
                    String stopTab=ztab+"_STOP";
                    String impTab=ztab+"_I";
                    /*for(int i=0; i<=10000; i++){
                        try{
                            if(!session.tableExists(impTab)){
                                session.getForDDL().createImportTableFromTable(ztab, impTab);
                            }
                            break;
                        } catch (Exception e){
                            throw e;
                        }
                    }*/
                    for(int i=0; i<=10000; i++){
                        try{
                            if(!session.tableExists(stopTab)){
                                session.getForDDL().createStopTableFromTable(ztab, stopTab);
                            }
                            break;
                        } catch (Exception e){
                            throw e;
                        }
                    }
                }

            }
        });
    }

}
